﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace CourseScheduling.DataAccess
{
    public class CSDataManager
    {
        private string conString = null;
        private SqlConnection conn;
        private SqlDataReader rdr;
        private SqlTransaction tran;
        private List<string> fieldnames = new List<string>();

        public CSDataManager()
        {
        }

        public CSDataManager(string conString)
        {
            this.conString = conString;
        }

        public List<string> getFieldNames()
        {
            return this.fieldnames;
        }

        private string getConnectionString()
        {
            string connectionString = null;
            if (!String.IsNullOrEmpty(conString))
            {
                connectionString = this.conString;
            }
            else
            {
                connectionString = "Data Source=lyra.ccec.unf.edu;Initial " +
                    "Catalog=cop4710fall2011dma1;Persist Security Info=True;User ID=cop4710fall2011dma1;Password=xkqED0cl7e";
            }
            return connectionString;
        }

        public SqlConnection getDataConnection(bool isTransaction)
        {
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                if (isTransaction)
                {
                    tran = conn.BeginTransaction();
                }
            }
            catch (Exception ex)
            {
                throw new System.ArgumentException("Cannot open the connection: ", getConnectionString(), ex);
            }
            return conn;
        }

        public void createTableRow(string strTable, CSDataObject da)
        {
            String sqlString = "INSERT INTO " + strTable + " ";
            sqlString += createColValueString(da);
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;
            }
        }

        private String createWhereString(CSDataObject daOb)
        {
            String where = null;
            foreach (var key in daOb.Keys)
            {
                if (daOb[key] is int)
                {
                    where += key + "=" + daOb[key].ToString() + " and ";
                }
                else
                {
                    where += key + "='" + daOb[key].ToString() + "' and ";
                }

            }

            where = where.Substring(0, where.Length - 4);
            return where;
        }

        private string updateString(CSDataObject daOb)
        {
            String str = "SET ";
            String fields = "";
            String values = "";

            //SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            foreach (String x in daOb.Keys)
            {
                object o = daOb[x];
                if (o is string)
                {
                    o = ((string)o).Replace("'", "''");
                }
                fields = x + "=";
                values = "'" + o + "',";
                str += fields + values;
                //values += ec[x] + ",\n";
            }
            char[] trimchar = { ',', '\n' };
            str = str.TrimEnd(trimchar);
            //values = values.TrimEnd(trimchar) + ")";
            // += fields + values;
            return str;
        }

        private String createColValueString(CSDataObject ec)
        {
            String str = "";
            String fields = "(";
            String values = "values(";
            //SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            foreach (String x in ec.Keys)
            {
                object o = ec[x];
                if (o is string)
                {
                    o = ((string)o).Replace("'", "''");
                }
                fields += x + ",";
                values += "'" + o + "',";
                //values += ec[x] + ",\n";
            }
            char[] trimchar = { ',', '\n' };
            fields = fields.TrimEnd(trimchar) + ") ";
            values = values.TrimEnd(trimchar) + ")";
            str += fields + values;
            return str;
        }

        private List<CSType> getDataObjects<CSType>(SqlCommand cmd) where CSType : CSDataObject, new()
        {
            cmd.CommandType = CommandType.Text;
            List<CSType> daObs = new List<CSType>();
            try
            {
                //execute the query
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    CSType daOb = new CSType();
                    fieldnames.Clear();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        this.fieldnames.Add(rdr.GetName(i).ToString());
                        daOb.Add(fieldnames[i], rdr[fieldnames[i]]);

                    }

                    daObs.Add(daOb);
                }
            }
            catch (Exception ex)
            {
                String x = ex.Message;
            }
            finally
            {
                rdr.Close();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return daObs;
        }

        public List<CSCourseRequest> getStudentRequests()
        {
            String sqlString = "Select * from StudentRequest";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            return getDataObjects<CSCourseRequest>(cmd);
        }

        public List<CSCourseRequest> getStudentRequest(String nnum, String term, int year,  String cn )
        {
            String sqlString = "Select * from StudentRequest where NNumber = '" +
                nnum + "' and semesterRequest = '" + term + "' and yearRequest = " +
                year + " and CourseNumber = '" + cn + "'";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            return getDataObjects<CSCourseRequest>(cmd);
        }

        public void createStudentRequest(CSCourseRequest csr)
        {
            List<CSCourseRequest> myCourses = getStudentRequest(csr.NNumber, csr.Semester, csr.Year, csr.CourseCode);
            if (myCourses.Count > 0)
            {
                try
                {
                    updateStudentRequest(csr);
                }
                catch (SqlException ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
            else
            {
                String sqlString = "INSERT INTO StudentRequest ";
                sqlString += createColValueString(csr) + ";";
                SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;
                try
                {
                    int z = cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (SqlException ex)
                {
                    tran.Rollback();
                    throw ex;

                }
            }
        }

        public void updateStudentRequest(CSCourseRequest csr)
        {
            String sqlString = "UPDATE StudentRequest " +
                updateString(csr) + " where NNumber = '" +
                csr.NNumber + "' and semesterRequest = '" + csr.Semester + "' and yearRequest = " +
                csr.Year + " and CourseNumber = '" + csr.CourseCode + "'";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;
            }
        }

        public int createProfPref(CSProfessorPreference cpp)
        {
            int z = 0;
            String sqlString = "INSERT INTO Preference ";
            sqlString += createColValueString(cpp) + ";";
            sqlString += "Select Scope_Identity();";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                z = int.Parse(cmd.ExecuteScalar().ToString());
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;

            }
            return z;
        }

        public void updateProfPref(int id, CSProfessorPreference cpp)
        {
            String sqlString = "UPDATE Preference " +
                    updateString(cpp) + " where id=" + id.ToString();
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;
            }
        }

        public List<CSCourse> getCourses()
        {
            String sqlString = "Select * from Course";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            return getDataObjects<CSCourse>(cmd);
        }

        public CSCourse getCourse(string cn)
        {
            String sqlString = "Select * from Course where CourseNumber = '" + cn + "'";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            List<CSCourse> tList = getDataObjects<CSCourse>(cmd);
            if (tList.Count == 0)
            {
                return null;
            }

            return tList[0];
        }

        public CSDayOfWeek getDOW(string id)
        {
            String sqlString = "Select * from DaysOfWeek where id=" + id;
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            List<CSDayOfWeek> tList = getDataObjects<CSDayOfWeek>(cmd);
            if (tList.Count == 0)
            {
                return null;
            }

            return tList[0];
        }

        public void createCoursePreference(CSCoursePreference cpr)
        {
            String sqlString = "INSERT INTO ProfCourseRank ";
            sqlString += createColValueString(cpr) + ";";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;

            }      
        }

        public List<CSCoursePreference> getCoursePreference(int id)
        {
            String sqlString = "Select * from ProfCourseRank where PreferenceID = " + id;
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            List<CSCoursePreference> tList = getDataObjects<CSCoursePreference>(cmd);
            return tList;
        }

        public void deleteCoursePreference(int id)
        {
            String sqlString = "delete from ProfCourseRank ";
            sqlString += " where PreferenceID=" + id  + ";";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;

            }
        }

        public void createDayOfWeekRank(DaysOfWeekPreference dowp)
        {
            String sqlString = "INSERT INTO DayOfWeekRank ";
            sqlString += createColValueString(dowp) + ";";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;

            }
        }

        public void deleteDayOfWeekRank(int id)
        {
            String sqlString = "DELETE FROM DayOfWeekRank ";
            sqlString += "where PreferenceID=" + id + ";";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(true));
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            try
            {
                int z = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                throw ex;

            }
        }
        
        public List<CSProfessorPreference> getProfRequests()
        {
            String sqlString = "Select * from Preference";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            return getDataObjects<CSProfessorPreference>(cmd);
        }

        public List<CSProfessorPreference> getProfRequests(int id)
        {
            String sqlString = "Select * from Preference where id=" + id;
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            return getDataObjects<CSProfessorPreference>(cmd);
        }

        public List<CSDayOfWeek> getDayOFWeek()
        {
            String sqlString = "Select * from DaysOfWeek";
            SqlCommand cmd = new SqlCommand(sqlString, getDataConnection(false));
            return getDataObjects<CSDayOfWeek>(cmd);
        }
    }
}
